﻿using System;
using System.IO;
using Community.CsharpSqlite.SQLiteClient;

namespace Netfraction.Common.DataStorage
{
	public class SqlDataStore : DataStore, IDataStore
	{
		private SqliteConnection _conn = null; 

		/// <summary>
		/// Creates a new SqlDataStore instance.
		/// </summary>
		/// <param name="owner"></param>
		public SqlDataStore(DataSource owner)
			: base(owner)
		{
			this.DataStoreType = DataStoreType.SqlDatabase;
		}

		/// <summary>
		/// Creates a new SqlDataStore instance and the specified data store. Does not overwrite an existing store.
		/// </summary>
		/// <param name="owner"></param>
		/// <param name="storeName"></param>
		public SqlDataStore(DataSource owner, string storeName)
			: base(owner)
		{
			this.DataStoreType = DataStoreType.SqlDatabase;
			this.DataStoreName = storeName;

			Create(storeName, false);
		}

		public bool Create(string name, bool overwrite)
		{
			if (!CheckBasePath(name))
				return false;

			if (!overwrite && File.Exists(Path.Combine(this.BasePath, name)))
				return true;

			if (!Directory.Exists(Path.GetDirectoryName(Path.Combine(this.BasePath, name))))
				Directory.CreateDirectory(Path.GetDirectoryName(Path.Combine(this.BasePath, name)));

			FileStream f = File.Create(Path.Combine(this.BasePath, name));
			f.Close();
			this.DataStoreName = name;

			return true;
		}

		public SqliteConnection Connect()
		{
			if (this.DataStoreName == string.Empty)
				return null;

			if (!File.Exists(this.FullPath))
				return null;

			return Connect(this.FullPath);
		}

		private SqliteConnection Connect(string dbPath)
		{
			try
			{
				// default behaviour is to create the database if it doesn't exist
				_conn = new SqliteConnection("uri=" + new Uri(dbPath, UriKind.RelativeOrAbsolute).ToString().Replace("file:///", "file://"));
				_conn.Open();
			}
			catch (Exception e)
			{
				Netfraction.Common.Logging.LogManager.WriteEntry(e.Message, Netfraction.Common.Logging.LogEntryType.Error, Netfraction.Common.Logging.LogEventId.SqlStoreException, Netfraction.Common.Logging.LogLevel.Detailed);
				return null;
			}

			return _conn;
		}

		private SqliteConnection CheckForOpenConnection()
		{
			if (_conn != null && _conn.State == System.Data.ConnectionState.Open)
				return _conn;
			else
				return Connect();
		}

		public void Disconnect()
		{
			if (CheckForOpenConnection() != null)
				_conn.Close();
		}

		public bool TablesExist(string[] tableNames)
		{
			int foundCount = 0;
			int reqCount = tableNames.Length;

			using (SqliteDataReader sdr = ExecuteQuery("SELECT name FROM sqlite_master WHERE type='table'"))
			{
				if (sdr == null)
					return false;

				while (sdr.Read())
				{
					try
					{
						for (int i = 0; i < tableNames.Length; i++)
						{
							if (sdr.GetString(0) == tableNames[i])
								foundCount++;
						}
						if (foundCount == reqCount)
							return true;
					}
					catch (InvalidCastException)
					{
						return false;
					}
					catch (Exception)
					{
						return false;
					}
				}
			}
			return false;
		}

		public bool TableExists(string tableName)
		{
			return TablesExist(new string[] { tableName });
		}

		public SqliteDataReader ExecuteQuery(string query)
		{
			return ExecuteQuery(new SqliteCommand(query, _conn));
		}

		public SqliteDataReader ExecuteQuery(SqliteCommand cmd)
		{
			cmd.Connection = CheckForOpenConnection();
			if (cmd.Connection == null)
				return null;

			SqliteDataReader retVal = null;
			try
			{
				retVal = cmd.ExecuteReader();
				cmd.Dispose();
			}
			catch (SqliteExecutionException e)
			{
				Netfraction.Common.Logging.LogManager.WriteEntry(e.Message, Netfraction.Common.Logging.LogEntryType.Error, Netfraction.Common.Logging.LogEventId.SqlStoreException, Netfraction.Common.Logging.LogLevel.Detailed);
			}
			return retVal;
		}

		public int ExecuteNonQuery(string query)
		{
			return ExecuteNonQuery(new SqliteCommand(query, _conn));
		}

		public int ExecuteNonQuery(SqliteCommand cmd)
		{
			cmd.Connection = CheckForOpenConnection();
			if (cmd.Connection == null)
				return 0;

			int retVal = 0;
			try
			{
				retVal = cmd.ExecuteNonQuery();
				cmd.Dispose();
			}
			catch (SqliteExecutionException e)
			{
				Netfraction.Common.Logging.LogManager.WriteEntry(e.Message, Netfraction.Common.Logging.LogEntryType.Error, Netfraction.Common.Logging.LogEventId.SqlStoreException, Netfraction.Common.Logging.LogLevel.Detailed);
			}
			return retVal;
		}

		public object ExecuteScalar(string query)
		{
			return ExecuteScalar(new SqliteCommand(query, _conn));
		}

		public object ExecuteScalar(SqliteCommand cmd)
		{
			cmd.Connection = CheckForOpenConnection();
			if (cmd.Connection == null)
				return 0;

			object retVal = null;
			try
			{
				retVal = cmd.ExecuteScalar();
				cmd.Dispose();
			}
			catch (SqliteExecutionException e)
			{
				Netfraction.Common.Logging.LogManager.WriteEntry(e.Message, Netfraction.Common.Logging.LogEntryType.Error, Netfraction.Common.Logging.LogEventId.SqlStoreException, Netfraction.Common.Logging.LogLevel.Detailed);
			}
			catch (Exception e)
			{
				Netfraction.Common.Logging.LogManager.WriteEntry(e.Message, Netfraction.Common.Logging.LogEntryType.Error, Netfraction.Common.Logging.LogEventId.SqlStoreException, Netfraction.Common.Logging.LogLevel.Detailed);
			}
			return retVal;
		}

		public bool Delete()
		{
			if (this.DataStoreName == string.Empty)
				return false;

			File.Delete(this.FullPath);

			return File.Exists(this.FullPath);
		}

		public static int CastInt64ToInt(Int64 num)
		{
			if (num > int.MinValue && num < int.MaxValue)
				return Convert.ToInt32(num);
			else
				return 0;
		}
	}
}
